package com.tang.sharding.schedule.task.jdbc;

import com.tang.sharding.enums.Epoch;
import com.tang.sharding.enums.ShardingCycle;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Optional;
import java.util.StringJoiner;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

/**
 * @author Tang
 * @classname JdbcManager
 * @description [ jdbc 操作 ]
 * @date 2021/8/2 11:26
 */
@Slf4j
public class JdbcManager {

    interface SQL {
        String SHOW_TABLE_SQL = "SHOW CREATE TABLE %s;";
        String SHOW_TABLE_SQL_COLUMN = "Create Table";

        int TIMEOUT = 5;
        int MAX_TIME = 5;
    }

    private final DataSource dataSource;
    private static Connection connection;
    private int currentTime;
    private final ScheduledExecutorService scheduledExecutorService = Executors.newSingleThreadScheduledExecutor();

    public JdbcManager(DataSource dataSource) {
        this.dataSource = dataSource;
        refresh();
    }

    /**
     * 获取表的sql
     *
     * @param tableName    表名
     * @param newTableName newTableName
     * @return
     */
    public String getTableSql(String tableName, String newTableName) {
        String sql = String.format(SQL.SHOW_TABLE_SQL, tableName);
        try {
            ResultSet resultSet = connection.createStatement().executeQuery(sql);
            if (resultSet != null && resultSet.next()) {
                return Optional.ofNullable(resultSet.getString(SQL.SHOW_TABLE_SQL_COLUMN))
                        .map(oldSql -> {
                            return oldSql.replace(tableName, newTableName);
                        }).orElse("");
            }
        } catch (SQLException throwables) {
            log.error("获取表：[{}]的SQL信息失败了,可能是它并不存在,失败原因：", tableName, throwables);
        }
        return "";
    }

    /**
     * 要创建的 new table name
     *
     * @param tableName
     * @param shardingCycle
     * @param epoch
     * @return
     */
    public String getNewTableName(String tableName, ShardingCycle shardingCycle, Epoch epoch) {
        StringJoiner stringJoiner = new StringJoiner("_");
        stringJoiner.add(tableName);
        LocalDate now = LocalDate.now();
        switch (shardingCycle) {
            case DAY:
                now = now.plusDays(epoch.getValue());
                stringJoiner.add(now.getYear() + "");
                stringJoiner.add(now.getMonthValue() + "");
                stringJoiner.add(now.getDayOfMonth() + "");
                break;
            case WEEK:
                now = now.plusWeeks(epoch.getValue());
                DayOfWeek dayOfWeek = now.getDayOfWeek();
                LocalDate localDate = now.plusDays(-dayOfWeek.getValue() + 1);
                stringJoiner.add(localDate.getYear() + "");
                stringJoiner.add(localDate.getMonthValue() + "");
                stringJoiner.add(localDate.getDayOfMonth() + "");
                break;
            case MONTH:
                now = now.plusMonths(epoch.getValue());
                stringJoiner.add(now.getYear() + "");
                stringJoiner.add(now.getMonthValue() + "");
                break;
            case YEAR:
                now = now.plusYears(epoch.getValue());
                stringJoiner.add(now.getYear() + "");
                break;
            default:
                break;
        }
        return stringJoiner.toString();
    }

    /**
     * 检测表
     *
     * @param tableName 表名
     * @return
     */
    public boolean checkTable(String tableName) {
        try {
            synchronized (this) {
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet resultSet = metaData.getTables(null, connection.getCatalog(), tableName, new String[]{"TABLE"});
                if (resultSet != null && resultSet.next()) {
                    log.warn("已检测到table:[{}]已经存在...不会重复创建...", tableName);
                    return true;
                }
            }
        } catch (SQLException e) {
            log.error("表检测:[{}],不存在...", tableName, e);
        }
        return false;
    }

    /**
     * 创建表
     *
     * @param tableName 表名
     * @param sql       sql
     */
    public void createTable(String tableName, String sql) {
        try {
            connection.createStatement().executeUpdate(sql);
            connection.commit();
            log.info("执行sql:{},table:[{}]创建成功...", sql, tableName);
        } catch (SQLException throwables) {
            log.error("执行sql:{},table:[{}]创建失败...", sql, tableName);
        }
    }

    /**
     * 获取连接
     */
    private void refresh() {
        try {
            if (connection == null || connection.isValid(SQL.TIMEOUT)) {
                connection = dataSource.getConnection();
                connection.setAutoCommit(false);
                // 连接保活
                keepLive();
            }
        } catch (Exception e) {
            log.error("数据库连接获取失败!失败原因:{},准备重试,当前重试次数:{}", e,this.currentTime);
            tryRefresh();
        }
    }

    /**
     * 刷新
     */
    private void tryRefresh() {
        if (currentTime++ <= SQL.MAX_TIME) {
            refresh();
        }
        this.currentTime = 0;
    }

    public void keepLive() {
        scheduledExecutorService.scheduleWithFixedDelay(new Keep(), 0L, 240L, TimeUnit.MINUTES);
    }

    @Slf4j
    public static class Keep implements Runnable{
        @Override
        public void run() {
            try {
                connection.createStatement().executeQuery("select 1");
                connection.commit();
                log.info("连接保活执行了..." + LocalDateTime.now());
            } catch (SQLException throwables) {
                log.error("保活执行错误!:{}",throwables);
            }
        }
    }

}